<?php 
    class StudentInformationDAL{
        //获取学生列表
        public function getStudentList($instructorId,$department,$grade,$major,$class,$gender,$search,$page,$pageSize){
            $query_found_rows='select FOUND_ROWS() as count;';
            $query="select SQL_CALC_FOUND_ROWS student_id,name,gender,grade,tb_major_list.major_name 
            from tb_student_info_list,tb_major_list,tb_department_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and tb_major_list.department_id = tb_department_list.department_id ".
            ($department==''?'':"and tb_department_list.department_name=:department_name ").
            ($instructorId==0?'':"and tb_student_info_list.instructor_id=:instructor_id ").
            ($grade==0?'':"and tb_student_info_list.grade=:grade ").
            ($major==0?'':"and tb_student_info_list.major_id=:major ").
            ($class==0?'':"and tb_student_info_list.class=:class ").
            ($gender==0?'':'and tb_student_info_list.gender=:gender ').
            ($search==''?'':' and (tb_student_info_list.student_id=:search_id or tb_student_info_list.name=:search_name)').
            "order by grade,student_id LIMIT :page,:pageSize;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            if($instructorId!='')
                $statement->bindValue(":instructor_id",$instructorId);
            if($department!='')
                $statement->bindValue(":department_name",$department);
            if($grade!=0)
                $statement->bindValue(":grade",$grade);
            if($major!=0)
                $statement->bindValue(":major",$major);
            if($class!=0)
                $statement->bindValue(":class",$class);
            if($gender!=0){
                $statement->bindValue(":gender",$gender);
            }
            if($search!=''){
                $statement->bindValue(":search_id",$search);
                $statement->bindValue(":search_name",$search);
            }

            $statement->bindValue(":page",$page,PDO::PARAM_INT);
            $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
            if($statement->execute()){
                $studentList=$statement->fetchALL(PDO::FETCH_ASSOC);
                $row=$pdo->query($query_found_rows);
                $count=$row->fetch(PDO::FETCH_ASSOC);
                $row->closeCursor();

                $result=array(
                    'studentList'=>$studentList,
                    'count'=>$count['count']
                );
            }
            $statement->closeCursor();

            return $result;
        }

        public function getStudentDetail($studentId){
            $query="select student_id,name,gender,grade,tb_major_list.major_name,class,phone_number 
            from tb_student_info_list,tb_major_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and tb_student_info_list.student_id=:student_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":student_id",$studentId);
            if($statement->execute()){
                $studentInfo=$statement->fetch(PDO::FETCH_ASSOC);
            }
            return $studentInfo;
        }

        public function getStudentDormitoryInfo($studentId){
            $query="select student_id,tb_apartment_info.apartment_id,tb_apartment_info.apartment_name,floor,dormitory_id 
            from tb_student_dormitory_roster,tb_apartment_info 
            where tb_student_dormitory_roster.status=1 
            and tb_student_dormitory_roster.apartment_id=tb_apartment_info.apartment_id 
            and tb_student_dormitory_roster.student_id=:student_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":student_id",$studentId);
            if($statement->execute()){
                $dormitoryInfo=$statement->fetch(PDO::FETCH_ASSOC);
            }
            return $dormitoryInfo;
        }
    }
?>